SpdrAllocationNotice

V8 Message Definiton

METADATA

Attribute	Value
Topic	3695-order-allocation
MLink Token	SystemData
Product	SRTrade
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
allocNumber	BIGINT	PRI	0	unique number assigned to this allocation notice
tradeDate	DATE		'1900-01-01'
clientFirm	VARCHAR(16)		''
omniAccnt	VARCHAR(16)		''	omnibus accnt SR accnt
secKey_at	enum - AssetType		'None'	security key stock future or option
secKey_ts	enum - TickerSrc		'None'	security key stock future or option
secKey_tk	VARCHAR(12)		''	security key stock future or option
secKey_yr	SMALLINT UNSIGNED		0	security key stock future or option
secKey_mn	TINYINT UNSIGNED		0	security key stock future or option
secKey_dy	TINYINT UNSIGNED		0	security key stock future or option
secKey_xx	DOUBLE		0	security key stock future or option
secKey_cp	enum - CallPut		'Call'	security key stock future or option
secType	enum - SpdrKeyType		'None'	security type
mlegLegKey	TINYTEXT		''	secKeyStringKeylegRatiosidepositionEffect secKeyStringKeylegRatiosidepositionEffect
orderSide	enum - BuySell		'None'	order side always buy for MLEG orders
ssaleFlag	enum - ShortSaleFlag		'None'	ssale flag None if not stock
positionType	enum - PositionType		'None'	positionType None if not equity option
noticeState	enum - NoticeState		'None'	indicates that this noticed is ready to forward to a custodian
noticeText	TINYTEXT		''
allocMethod	enum - AllocMethod		'None'	method for delivery of the allocation instruction to the broker
execBrkrCode	VARCHAR(16)		''	used for FIX routing can be null
allocExDest	VARCHAR(16)		''	used for FIX routing
orderShape	enum - SpdrOrderShape		'None'	Single or MLeg
cumFillQuantity	INT		0	total fill size of all ChildOrders
avgFillPrice	DOUBLE		0	parent order average fill price
netMoney	DOUBLE		0	net allocation money
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification
ChildOrdersList	JSON		'JSON_OBJECT()'
FragmentsList	JSON		'JSON_OBJECT()'
LegPosTypeList	JSON		'JSON_OBJECT()'
OrderLegsList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
allocNumber	1

JSON Block (ChildOrdersList)

Field	Type	Comment
clOrdId	long
orderSize	int
cumFillQuantity	int
avgFillPrice	double
streetClOrdId	string	usually clOrdIDX16 but can be different if street gateway was translating clOrdId
streetOrderId	string	street side order ID for canceling order

JSON Block (FragmentsList)

Field	Type	Comment
accnt	string	SR alloc accnt
clientFirm	string	SR client firm
allocAccnt	string	clearing firm alloc accnt maps to FixAllocAccount
allocFragId	long	unique ID of this allocfragment maps to FixIndividualAllocID
allocSize	int	accnt alloc size

JSON Block (LegPosTypeList)

Field	Type	Comment
secKey	OptionKey
secType	enum - secType
accnt	string	SR alloc accnt matches fragment
clientFirm	string	SR client firm
ssaleFlag	enum - ssaleFlag	ssale flag if secType Stock
positionType	enum - positionType	positionType if secType Stock
firmPosition	int

JSON Block (OrderLegsList)

Field	Type	Comment
secKey	OptionKey
secType	enum - secType
mult	ushort
side	enum - side
legId	long	SR leg Id
altLegId	string	client leg Id usually from a FIX order
sumLegQty	int
sumLegMny	double

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRTrade`.`MsgSRAllocationNotice` (
    `allocNumber` BIGINT NOT NULL DEFAULT 0 COMMENT 'unique number assigned to this allocation notice',
    `tradeDate` DATE NOT NULL DEFAULT '1900-01-01',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '',
    `omniAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'omnibus accnt (SR accnt)',
    `secKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'security key (stock, future, or option)',
    `secKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','ESX','ANY','CXE','DXE','NXAM','NXBR','NXDUB','NXLS','NXLDN','NXML','NXMLT','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'security key (stock, future, or option)',
    `secKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'security key (stock, future, or option)',
    `secKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_xx` DOUBLE NOT NULL DEFAULT 0 COMMENT 'security key (stock, future, or option)',
    `secKey_cp` ENUM('Call','Put','Pair') NOT NULL DEFAULT 'Call' COMMENT 'security key (stock, future, or option)',
    `secType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'security type',
    `mlegLegKey` TINYTEXT NOT NULL DEFAULT '' COMMENT '[secKey.StringKey;legRatio;side;positionEffect, secKey.StringKey;legRatio;side;positionEffect, ...]',
    `orderSide` ENUM('None','Buy','Sell') NOT NULL DEFAULT 'None' COMMENT 'order side (always buy for MLEG orders)',
    `ssaleFlag` ENUM('None','Long','Short','Exempt','Auto','Open','Close','Cover','NA') NOT NULL DEFAULT 'None' COMMENT 'ssale flag (None if not stock)',
    `positionType` ENUM('None','Opening','Closing','Auto') NOT NULL DEFAULT 'None' COMMENT 'positionType (None if not equity option)',
    `noticeState` ENUM('None','ReadyToSend','HoldForReview','Canceled','Error') NOT NULL DEFAULT 'None' COMMENT 'indicates that this noticed is ready to forward to a custodian',
    `noticeText` TINYTEXT NOT NULL DEFAULT '',
    `allocMethod` ENUM('None','FIX','FilePAS') NOT NULL DEFAULT 'None' COMMENT 'method for delivery of the allocation instruction to the broker',
    `execBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'used for FIX routing (can be null)',
    `allocExDest` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'used for FIX routing',
    `orderShape` ENUM('None','Single','Cross','MLeg','MLegCross') NOT NULL DEFAULT 'None' COMMENT 'Single or MLeg',
    `cumFillQuantity` INT NOT NULL DEFAULT 0 COMMENT 'total fill size of all ChildOrders',
    `avgFillPrice` DOUBLE NOT NULL DEFAULT 0 COMMENT 'parent order average fill price',
    `netMoney` DOUBLE NOT NULL DEFAULT 0 COMMENT 'net allocation money',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    `ChildOrdersList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(ChildOrdersList)),
    `FragmentsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(FragmentsList)),
    `LegPosTypeList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(LegPosTypeList)),
    `OrderLegsList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(OrderLegsList)),
    PRIMARY KEY USING HASH (`allocNumber`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `allocNumber`,
    `tradeDate`,
    `clientFirm`,
    `omniAccnt`,
    `secKey_at`,
    `secKey_ts`,
    `secKey_tk`,
    `secKey_yr`,
    `secKey_mn`,
    `secKey_dy`,
    `secKey_xx`,
    `secKey_cp`,
    `secType`,
    `mlegLegKey`,
    `orderSide`,
    `ssaleFlag`,
    `positionType`,
    `noticeState`,
    `noticeText`,
    `allocMethod`,
    `execBrkrCode`,
    `allocExDest`,
    `orderShape`,
    `cumFillQuantity`,
    `avgFillPrice`,
    `netMoney`,
    `timestamp`,
    `ChildOrdersList`,
    `FragmentsList`,
    `LegPosTypeList`,
    `OrderLegsList`
FROM `SRTrade`.`MsgSRAllocationNotice`
WHERE 
    /* Replace with a BIGINT */ 
    `allocNumber` = 1234567890;

Doc Columns Query

SELECT * FROM SRTrade.doccolumns WHERE TABLE_NAME='SpdrAllocationNotice' ORDER BY ordinal_position ASC;